/**to generate database table bean and read/update/delete operation source file
 * by fuzhou huilin corp.ltd @author lms 2016.07.08
 * update by lms 2016.07.09 to remember neboat wind
 * update by lms 2017.05.23 for support get table field info
 * update by lms 2017.12.18 for new code style
 */

package ms.core.db.mysql;

import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import ms.core.tool.DateTimeTool;
import ms.core.tool.StrTool;
import ms.core.tool.TemplateTool;

public class CodeHelp {
	private static String tplPath = "/template/";
	
	IDbDrv db=null;						//数据库连接对象
	String dbName;						//当前连接数据库名称
	String savePath;					//bean、dao、utils 以及 service 父目录完整路径
	Map<String, String> variants;		//变量列表

	public CodeHelp(String corpName, String packageName, String savePath){
		try {
			db = new DbUtilsDrv();
			dbName  = db.getDatabaseName();
			this.savePath= savePath;

			variants = new HashMap<String, String>();
			variants.put("${company}", corpName);
			variants.put("${package}", packageName);
			variants.put("${author}", System.getenv("USERNAME"));
			variants.put("${createTime}", DateTimeTool.dateToDateTimeString(new Date()));
		}catch(Exception e) {
			e.printStackTrace();
		}
	}

	public void dispose(){
		if (db!=null) this.db.closeConnection();
		db = null;
	}

	/**
	 * 移除下划线，并实现驼峰式命名，如：sys_user=>sysUser
	 * 
	 * @param table
	 * @return
	 */
	private String removeFirstUnderlineStr(String table){
		String str = table;
		int pos = table.indexOf("_");
		if (pos>=0) str = StrTool.rightSubString(str, str.length()-(pos+1));
		
		return removeUnderline(str);
	}

	/**
	 * 移除下划线，并实现驼峰式命名，如：sys_user=>sysUser
	 * 
	 * @param table
	 * @return
	 */
	private String removeUnderline(String table){
		int i = table.indexOf("_");
		while (i>=0){
			table = StrTool.upperCasePositionTitle(table, i+1);
			table = table.replaceFirst("_", "");
			i = table.indexOf("_");
		}
		return table;
	}

////////////////////////////////////////for generate bean code ///////////////////////////////////////////////

	/**获取表字段信息
	 * @param table table name of generating bean
	 * @return field declarations text
	 */
	private List<Map<String, String>> getFieldInfo(String table){
		List<Map<String, String>> rets = new ArrayList<Map<String, String>>();
		try{
			String paramDesc = "";
			DatabaseMetaData metadata = this.db.getConnection().getMetaData();
			ResultSet rsColumns = metadata.getColumns(dbName, null, table, null);
			while (rsColumns.next()){
				Map<String, String> item = new HashMap<String, String>();
				String field = rsColumns.getString(4);
				item.put("${fieldName}", field);
				item.put("${fieldNameEx}", StrTool.upperCaseTitle(field));
				item.put("${fieldDesc}", rsColumns.getString(12));
				paramDesc += "\""+field+"\":";

				switch(rsColumns.getInt(5)){
				case java.sql.Types.BIT:
					item.put("${fieldType}","Byte");
					paramDesc += rsColumns.getString(12)+",";
					break;
				case java.sql.Types.TINYINT:
					item.put("${fieldType}","Short");
					paramDesc += rsColumns.getString(12)+",";
					break;
				case java.sql.Types.INTEGER:
					item.put("${fieldType}","Integer");
					paramDesc += rsColumns.getString(12)+",";
					break;
				case java.sql.Types.BIGINT:
					item.put("${fieldType}","Long");
					paramDesc += rsColumns.getString(12)+",";
					break;
				case java.sql.Types.BOOLEAN:
					item.put("${fieldType}","Boolean");
					paramDesc += rsColumns.getString(12)+",";
					break;
				case java.sql.Types.CHAR:
				case java.sql.Types.NCHAR:
					item.put("${fieldType}","char");
					paramDesc += "\""+rsColumns.getString(12)+"\",";
					break;
				case java.sql.Types.TIME:
				case java.sql.Types.DATE:
				case java.sql.Types.TIMESTAMP:
					item.put("${fieldType}","Date");
					paramDesc += "\""+rsColumns.getString(12)+"\",";
					break;
				case java.sql.Types.DECIMAL:
				case java.sql.Types.DOUBLE:
				case java.sql.Types.REAL:
				case java.sql.Types.FLOAT:
					item.put("${fieldType}","Double");
					paramDesc += rsColumns.getString(12)+",";
					break;
				case java.sql.Types.VARCHAR:
				case java.sql.Types.NVARCHAR:
				case java.sql.Types.LONGNVARCHAR:
				case java.sql.Types.LONGVARCHAR:
					item.put("${fieldType}","String");
					paramDesc += "\""+rsColumns.getString(12)+"\",";
					break;
				default: 
					break;
				}
				rets.add(item);
			}
			paramDesc = StrTool.deleteCharAt(paramDesc, paramDesc.length()-1);
			variants.put("${paramDesc}", paramDesc);
			rsColumns.close();
		}catch(Exception e){
			e.printStackTrace();
		}
		return rets;
	}
	
	private String putTableObject(String table) {
		// 表名称去掉下划线，首字母大写驼峰式名称.如 sys_user_info => SysUserInfo
		String tableName = StrTool.upperCaseTitle(removeUnderline(table));
		// 表名称去掉首个下划线之前字符串和所有下划线，首字母大写驼峰式名称.如 sys_user_info => UserInfo
		String objName = StrTool.upperCaseTitle(removeFirstUnderlineStr(table));
		// objName 首字母小写
		String obj = StrTool.lowerCaseTitle(objName);
		variants.put("${table}", table);
		variants.put("${tableName}", tableName);
		variants.put("${objName}", objName);
		variants.put("${obj}", obj);

		return tableName;
	}

	//to generate table's bean code
	public void makeBean(String table){
		String tableName = putTableObject(table);

		List<Map<String, String>> fields = getFieldInfo(table);

		String tplFile = tplPath + "bean.tpl";
		String beanFile = savePath+"/bean/"+tableName+"Bean.java";

		if (TemplateTool.makeFile(tplFile, beanFile, variants, fields))
			System.out.println("table "+table+" bean created!");
		else
			System.out.println("table "+table+" bean create failure!");
	}
	
//////////////////////////////////////// for generate CRUD code ///////////////////////////////////////////////

//-----------------------------------    generate dao code   ------------------------------------------------
	
	//to generate table's new\modify\delete\query operation
	public void makeDao(String table){
		String tableName = putTableObject(table);
		List<Map<String, String>> fields = getFieldInfo(table);
		
		String tplFile = tplPath + "dao.tpl";
		String daoFile = savePath+"/dao/"+tableName+"Dao.java";
		
		if (TemplateTool.makeFile(tplFile, daoFile, variants, fields))
			System.out.println("table "+table+" dao created!");
		else
			System.out.println("table "+table+" dao create failure!");
	}

	//-----------------------------------    generate utils code   ------------------------------------------------

	//to make utils
	public void makeUtils(String table){
		String tableName = putTableObject(table);
		List<Map<String, String>> fields = getFieldInfo(table);
		
		String tplFile = tplPath + "utils.tpl";
		String utilsFile = savePath+"/utils/"+tableName+"Utils.java";
		
		if (TemplateTool.makeFile(tplFile, utilsFile, variants, fields))
			System.out.println("table "+table+" utils created!");
		else
			System.out.println("table "+table+" utils create failure!");
	}

	//-----------------------------------    generate service code   ------------------------------------------------

	//to make service
	public void makeService(String table){
		String tableName = putTableObject(table);
		List<Map<String, String>> fields = getFieldInfo(table);
		
		String tplFile = tplPath + "service.tpl";
		String serviceFile = savePath+"/service/"+tableName+"Service.java";
		
		if (TemplateTool.makeFile(tplFile, serviceFile, variants, fields))
			System.out.println("table "+table+" service created!");
		else
			System.out.println("table "+table+" service create failure!");
	}

	//to make database all table's bean and CRUD
	public void makeCode(){
		try{
			String tbName;
			DatabaseMetaData metadata = this.db.getConnection().getMetaData();
			if (metadata==null) return;

			ResultSet tables = metadata.getTables(dbName, null, null, null);
			while (tables.next()) {   
				tbName = tables.getString(3);
				this.makeBean(tbName);
				this.makeDao(tbName);
				this.makeUtils(tbName);
				this.makeService(tbName);
			}
			tables.close();
		}
		catch(Exception e){
			e.printStackTrace();
		}
	}
	
	//table field definition
	public class Field{
		private String fieldName;
		private int fType;
		private String fTypeName;
		private String displayName;
		private int length;
		private int precision;
		private boolean allowNull;
		private boolean key;
		private boolean autoInc;
		
		public String getFieldName() { return fieldName; }
		public void setFieldName(String fieldName) { this.fieldName = fieldName; }
		
		public int getfType() { return fType; }
		public void setfType(int fType) { this.fType = fType; }
		
		public String getfTypeName() { return fTypeName; }
		public void setfTypeName(String fTypeName) { this.fTypeName = fTypeName; }
		
		public String getDisplayName() { return displayName; }
		public void setDisplayName(String displayName) { this.displayName = displayName; }

		public int getLength() { return length; }
		public void setLength(int length) { this.length = length; }
		
		public int getPrecision() { return precision; }
		public void setPrecision(int precision) { this.precision = precision; }
		
		public boolean isAllowNull() { return allowNull; }
		public void setAllowNull(boolean allowNull) { this.allowNull = allowNull; }
		
		public boolean isKey() { return key; }
		public void setKey(boolean key) { this.key = key; }

		public boolean isAutoInc() { return autoInc; }
		public void setAutoInc(boolean autoInc) { this.autoInc = autoInc; }
	}

	/**
	 * to get table field list
	 * @param table table name
	 * @return field list
	 * @author lms 2017.05.24
	 */
	public List<Field> getTableField(String table){
		List<Field> ret = null;
		try {
			PreparedStatement ps = this.db.getConnection().prepareStatement("select * from "+table+" where 1<0");
            ResultSet rs = ps.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();

            ret = new ArrayList<Field>();
            int count = rsmd.getColumnCount();
			for (int i=1; i<=count; i++){
				Field f = new Field();
				f.setFieldName(rsmd.getColumnName(i));
				f.setfType(rsmd.getColumnType(i));
				f.setfTypeName(rsmd.getColumnTypeName(i));
				f.setDisplayName(rsmd.getColumnLabel(i));
				f.setLength(rsmd.getPrecision(i));
				f.setPrecision(rsmd.getScale(i));
				f.setAllowNull(rsmd.isNullable(i)==ResultSetMetaData.columnNullable);
				f.setAutoInc(rsmd.isAutoIncrement(i));
				ret.add(f);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return ret;
	}

	public static void main(String[] args){
		CodeHelp hlp = new CodeHelp("鲸准科技", "hs.com","d://tmp");
		String tb = hlp.removeFirstUnderlineStr("sys_user_name");
		System.out.println(tb);

		//hlp.makeBean("sys_user");
		//hlp.makeDao("sys_role");
		//hlp.makeUtils("sys_role");
		//hlp.makeService("sys_role");
		//hlp.makeCode();
		//List<Field> list = hlp.getTableField("sys_role");
		//System.out.println(JsonTool.listBeanToJson(list));
		hlp.dispose();
	}
}
